<?php defined('SYSPATH') or die('No direct script access.');

class Playlists_Model extends Model {

	protected $temp_filename;
	
	public function __construct() {		
		$this->temp_filename = '';
		$this->db = Database::instance();
	}
	
	public function count_playlists()
    { 
		$query = $this->db->select('id')->get('playlists');
		return $query->count();
    }
    
    public function browse($limit, $offset)
    {
    	$sql = '
			SELECT * 
			FROM playlists 
			ORDER BY created DESC
			LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($offset, $limit));
		return $result;
    }

    public function load_albums() {
    	$sql = '
			SELECT * 
			FROM albums 
			ORDER BY title;
    	';
		$query = $this->db->query($sql);
		return $query->result(FALSE);
    }

	public function load_songs($album_id) {
    	$sql = '
			SELECT * 
			FROM songs WHERE album_id=?  
			ORDER BY title;
    	';
		$query = $this->db->query($sql,array($album_id));
		return $query->result(FALSE);
    }

	public function search_song($album_id,$track_no){
		$sql = '
					SELECT artists.id AS artist_id, artists.name AS artist_name, E. *
					FROM (

					SELECT artists_albums.artist_id, D. *
					FROM (

					SELECT albums.id, albums.title AS album_title, albums.cover_picture, B. *
					FROM (

					SELECT album_id, track_no, title AS song_title, duration, sample_file, low_bit_rate_file, high_bit_rate_file
					FROM songs
					WHERE album_id =?
					AND track_no =?
					) AS B
					LEFT JOIN albums ON albums.id = B.album_id
					) AS D
					LEFT JOIN artists_albums ON artists_albums.album_id = D.album_id
					) AS E
					LEFT JOIN artists ON artists.id = E.artist_id
    	';
		$query = $this->db->query($sql,array($album_id,$track_no));
		return $query->result(FALSE);
	}

	public function create_tracklist($songurl,$artist_name,$album_title,$song_title,$album_cover_picture){
		$result = '
			<track>
				<location>' .url::base().$songurl. '</location>
				<creator>'.$artist_name.'</creator>
				<album>'.$album_title.'</album>
				<title>'.$song_title.'</title>
				<image>'.url::base().$album_cover_picture.'</image>
				<info>http://www.gramma.com</info>
			</track>
			';
			
			return $result;

	}


    public function create_file_xml_playlist($intType,$strXML,$playlist_name_prefix)
     {
		$strFileName = 'media/upload/playlists/'.$playlist_name_prefix;
		if ($intType == 1){
			$strFileName .=  '_sample.xspf';  //'media/upload/playlists/'..'.xspf';
		}elseif ($intType == 2){
			$strFileName .=  '_low.xspf';
		}elseif ($intType == 3){
			$strFileName .=  '_high.xspf';
		}
        
		$objCreate = fopen($strFileName,'w');
		fwrite($objCreate,iconv('UTF-8', 'TIS-620', $strXML));
		if ($objCreate)
		 {
			return $strFileName;
		 }
		else
		 {
			return 0;
		 }
     }


	 public function file_validate($file_name,$strUID) {
	    $_FILES = Validation::factory($_FILES)
					->add_rules('picture', 
								'upload::valid', 
								'upload::type[gif,jpg,png]', 
								'upload::size[1M]');
		 
		if ($_FILES->validate())
		{
			$config = Kohana::config('hsm');
			
			// Temporary file name
			$this->temp_filename = upload::save($file_name);
			if (file_exists($this->temp_filename)) {
				// Resize, sharpen, and save the image
				Image::factory($this->temp_filename)
					->resize(250, 250, Image::AUTO)
					->save($config['upload_playlists'].$strUID.'_'.basename($this->temp_filename));
			 
				// Remove the temporary file
				unlink($this->temp_filename);
				$this->temp_filename = $config['upload_playlists'].$strUID.'_'.basename($this->temp_filename);
			}
			else {
				$this->temp_filename = '';
			}
		}
		return $_FILES;
    }

    public function get_filename() {
    	return $this->temp_filename;
    }

   public function save($data) {
		$this->db->query('BEGIN');
		try {
				$sql = 'INSERT INTO playlists(owner_id,title, description, playlist_logo, sample_file,low_bit_rate_file,high_bit_rate_file,created) VALUES(?,?,?,?,?,?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['owner_id'],$data['title'], $data['desc'], $data['logo'], $data['sample'], $data['low'], $data['high']));
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }

   public function save_edit($data,$playlist_id) {
		$this->db->query('BEGIN');
		try {
				$sql = 'UPDATE playlists SET title=?,description=?,sample_file=?,low_bit_rate_file=?,high_bit_rate_file=?,updated=CURRENT_TIMESTAMP()  WHERE id=?';
				$query = $this->db->query($sql, array($data['title'], $data['desc'],  $data['sample'], $data['low'], $data['high'],$playlist_id));
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }

   public function save_edit_picture($data,$playlist_id) {
		$this->db->query('BEGIN');
		try {
				$sql = 'UPDATE playlists SET title=?,description=?,playlist_logo=?,sample_file=?,low_bit_rate_file=?,high_bit_rate_file=?,updated=CURRENT_TIMESTAMP()  WHERE id=?';
				$query = $this->db->query($sql, array($data['title'], $data['desc'], $data['logo'], $data['sample'], $data['low'], $data['high'],$playlist_id));
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }

   public function save_playlist_song($data) {
		$this->db->query('BEGIN');
		try {
				$sql = 'INSERT INTO playlists_songs(playlist_id, album_id, track_no, order_no,created) VALUES(?,?,?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['playlist_id'], $data['album_id'], $data['track_no'], $data['order_no']));
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }

	public function find_playlist_id($sample_file){
		$sql = '
			SELECT id 
			FROM playlists WHERE sample_file=?;
    	';
		$query = $this->db->query($sql,array($sample_file));
		return $query->result(FALSE);
	}

	public function find_playlist_detail($playlist_id){
		$sql = '
			SELECT *
			FROM playlists WHERE id=?;
    	';
		$query = $this->db->query($sql,array($playlist_id));
		return $query->result(FALSE);
	}

	public function find_playlist_song($playlist_id){
		$sql = '
			SELECT * 
			FROM playlists_songs WHERE playlist_id=?
			ORDER BY order_no;
    	';
		$query = $this->db->query($sql,array($playlist_id));
		return $query->result(FALSE);
	}


    public function playlist_song_all($playlist_id) {
 		$this->db->query('BEGIN');
		try {
			//$config = Kohana::config('hsm');
			
			//$filename = $this->get_image_fileneme($id);
			//if (!empty($filename)) {
			//	$filename = $config['upload_albums'].basename($filename);
			//}

			$sql = 'DELETE FROM playlists_songs WHERE playlist_id=?;';
			
			$query = $this->db->query($sql, array($playlist_id));
			
			$this->db->query('COMMIT');
			
			//if (!empty($filename)) {
			//	unlink($filename);
			//}
			
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
    }

    public function delete($playlist_id) {
 		$this->db->query('BEGIN');
		try {
			$config = Kohana::config('hsm');

			$this->delete_song($playlist_id);
			$this->delete_channel_playlist($playlist_id);

			$sql = 'DELETE FROM playlists WHERE id=?;';
			
			$query = $this->db->query($sql, array($playlist_id));
			
			$this->db->query('COMMIT');
			

			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
    }

	public function delete_song($playlist_id){
 		$this->db->query('BEGIN');
		try {
			$config = Kohana::config('hsm');

			

			$sql = 'DELETE FROM playlists_songs WHERE playlist_id=?;';
			
			$query = $this->db->query($sql, array($playlist_id));
			
			$this->db->query('COMMIT');
			

			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
	}

	public function delete_channel_playlist($playlist_id){
 		$this->db->query('BEGIN');
		try {
			$config = Kohana::config('hsm');

			

			$sql = 'DELETE FROM channels_playlists WHERE playlist_id=?;';
			
			$query = $this->db->query($sql, array($playlist_id));
			
			$this->db->query('COMMIT');
			

			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	   	
	}




}